package com.example.jdbc.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.example.jdbc.domain.Book;

/**
 * Project:spring-boot-jdbc
 * <p>
 * Title:BookService2.java
 * <p>
 * Description:
 * <p>
 * Copyrigth:Jiang Software LTD.co Copyright (c) 2022
 * <p>
 * 
 * @version 1.0
 * @since 1.8
 * @author jiang
 * @history 2022 jiang create
 */
@Service
public class BookService2 {

	@Autowired
	private NamedParameterJdbcTemplate nameDao;

	public int create(int bookId, String bookName, String bookAddress) {
		String sql = "insert into t_book(bookId,bookName,bookAddress) values(:bookId,:bookName,:bookAddress)";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", bookId);
		params.addValue("bookName", bookName);
		params.addValue("bookAddress", bookAddress);
		return nameDao.update(sql, params);
	}

	public int create(Book book) {
		String sql = "insert into t_book(bookId,bookName,bookAddress) values(:bookId,:bookName,:bookAddress)";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", book.getBookId());
		params.addValue("bookName", book.getBookName());
		params.addValue("bookAddress", book.getBookAddress());
		return nameDao.update(sql, params);
	}

	public int update(int bookId, String bookName, String bookAddress) {
		String sql = "update t_book set bookName=:bookName,bookAddress=:bookAddress where bookId=:bookId";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", bookId);
		params.addValue("bookName", bookName);
		params.addValue("bookAddress", bookAddress);
		return nameDao.update(sql, params);
	}

	public int update(Book book) {
		String sql = "update t_book set bookName=:bookName,bookAddress=:bookAddress where bookId=:bookId";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", book.getBookId());
		params.addValue("bookName", book.getBookName());
		params.addValue("bookAddress", book.getBookAddress());
		return nameDao.update(sql, params);
	}

	public List<Book> findBookByName(String bookName) {
		String sql = "select bookId,bookName,bookAddress from t_book where bookName =:bookName";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookName", bookName);
		List<Book> results = nameDao.query(sql, params, 
				(resultSet, rowNum) -> new Book(resultSet.getInt("bookId"),
												resultSet.getString("bookName"), 
												resultSet.getString("bookAddress")

		));
		return results;
	}
	
	public List<Book> findBookByName2(String bookName) {
		String sql = "select bookId,bookName,bookAddress from t_book where bookName =:bookName";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookName", bookName);
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		List<Book> results = nameDao.query(sql, params, accountRowMapper);
		return results;
	}
	
	public Book findBookById(int bookId) {
		String sql = "select * from t_book where bookId =:bookId";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", bookId);
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		Book book = nameDao.queryForObject(sql, params, accountRowMapper);
		return book;
	}

	// 自定义映射使用
	public Book findBookById2(int bookId) {
		String sql = "select * from t_book where bookId =:bookId";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", bookId);
		BookRowMapper mapper = new BookRowMapper();
		Book book = nameDao.queryForObject(sql, params, mapper);
		return book;
	}

	public Book findBook(Book book) {
		String sql = "select bookId,bookName,bookAddress from t_book where bookId =:bookId or bookName=:bookName or bookAddress=:bookAddress";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", book.getBookId());
		params.addValue("bookName", book.getBookName());
		params.addValue("bookAddress", book.getBookAddress());
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		return nameDao.queryForObject(sql, params, accountRowMapper);
	}

	public List<Book> findAllBooks() {
		String sql = "select bookId,bookName,bookAddress from t_book";
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		List<Book> list = nameDao.query(sql, accountRowMapper);
		return list;
	}

	public int deleteById(int bookId) {
		String sql = "delete from t_book where bookId =:bookId";
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("bookId", bookId);
		return nameDao.update(sql, params);
	}

	public int getAllBooksCount() {
		String sql = "select count(1) from t_book";
		MapSqlParameterSource params = new MapSqlParameterSource();
		return nameDao.queryForObject(sql, params, Integer.class);
	}

	public int deleteAllBooks() {
		String sql = "delete from t_book";
		MapSqlParameterSource params = new MapSqlParameterSource();

		return nameDao.update(sql, params);
	}

}
